OpenEdge Development:<br />Programming Interfaces Adding XML data to a populated temp-table

Adding XML data to a populated temp-table

If you need to merge data in a 4GL temp-table with XML data, you can control the way in which Progress handles duplicate unique key conflicts with the read-mode parameter. In this example, a new temp-table is set up with definition from the Customer table and two new records. This set-up code is stored in an include file. Note that the first record uses a customer number that conflicts with one in the XML document. The second record has no conflict with the XML document:

/* pi-tfx-ttSetup-5.i / / Creates a new static temp-table and creates two new records for it. / DEFINE TEMP-TABLE ttCust / Field and index definitions. */ CREATE ttCust. ASSIGN ttCust.Name = "Extreme XML Sports" ttCust.CustNum = 1 ttCust.Balance = 111.11 ttCust.Country = "USA" ttCust.Comments = "Enthusiastic new customer!". CREATE ttCust. ASSIGN ttCust.Name = "Low-Impact XML Sports" ttCust.CustNum = 4 ttCust.Balance = 444.44 ttCust.Country = "USA" ttCust.Comments = "Mellow new customer.".

/* pi-tfx-ttSetup-5.i */ 
/* Creates a new static temp-table and creates two new records for it. */ 
DEFINE TEMP-TABLE ttCust 
   /* Field and index definitions. */ 
CREATE ttCust. 
ASSIGN  
    ttCust.Name = "Extreme XML Sports" 
    ttCust.CustNum = 1 
    ttCust.Balance = 111.11 
    ttCust.Country = "USA" 
    ttCust.Comments = "Enthusiastic new customer!". 
CREATE ttCust. 
ASSIGN  
    ttCust.Name = "Low-Impact XML Sports" 
    ttCust.CustNum = 4 
    ttCust.Balance = 444.44 
    ttCust.Country = "USA" 
    ttCust.Comments = "Mellow new customer.".

Shown below is an abbreviated copy of the ttCust.xml file.

<?xml version="1.0"?> <ttCust xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <ttCustRow> <CustNum>1</CustNum> <Country>USA</Country> <Name>Lift Tours</Name> ... </ttCustRow> <ttCustRow> <CustNum>2</CustNum> <Country>Finland</Country> <Name>Urpon Frisbee</Name> ,,, </ttCustRow> <ttCustRow> <CustNum>3</CustNum> <Country>USA</Country> <Name>Hoops </Name> ... </ttCustRow> </ttCust>

<?xml version="1.0"?> 
<ttCust xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <ttCustRow> 
    <CustNum>1</CustNum> 
    <Country>USA</Country> 
    <Name>Lift Tours</Name> 
    ... 
   </ttCustRow> 
  <ttCustRow> 
    <CustNum>2</CustNum> 
    <Country>Finland</Country> 
    <Name>Urpon Frisbee</Name> 
    ,,, 
</ttCustRow> 
  <ttCustRow> 
    <CustNum>3</CustNum> 
    <Country>USA</Country> 
    <Name>Hoops </Name> 
    ... 
</ttCustRow> 
</ttCust>

To illustrate the different results obtained with each read-mode option, there is a unique index defined for the CustNum field. There is a record in both the include file and the XML file that uses the same CustNum value (1). This is known as a duplicate unique key conflict. The different read modes respond to such a conflict in different ways.

The code sample below performs the same READ-XML( ) method call you saw in the last example. Replace the highlighted variable value in the code with each of the four read mode tokens and compare the results:

EMPTY

APPEND

MERGE

REPLACE

* pi-tfx-read-5.p / / Merges records in a temp-table with records from an XML file.*/ {pi-tfx-parameterVarDefs.i} `{pi-tfx-ttSetup-5.i}` DEFINE VARIABLE returnValue AS LOGICAL NO-UNDO. ASSIGN cSourceType = "FILE" cFile = "ttCust.xml" `cReadMode = "EMPTY"` cSchemaLocation = ? lOverrideDefaultMapping = ? cFieldTypeMapping = ? cVerifySchemaMode = ?. returnValue = `TEMP-TABLE ttCust:READ-XML`(cSourceType, cFile, cReadMode, cSchemaLocation, lOverrideDefaultMapping, cFieldTypeMapping, cVerifySchemaMode). IF returnValue THEN FOR EACH ttCust: DISPLAY CustNum NAME FORMAT "X(30)". END.

* pi-tfx-read-5.p */ 
/* Merges records in a temp-table with records from an XML file.*/ 
{pi-tfx-parameterVarDefs.i} 
{pi-tfx-ttSetup-5.i} 
DEFINE VARIABLE returnValue AS LOGICAL NO-UNDO. 
    
ASSIGN 
    cSourceType = "FILE" 
    cFile = "ttCust.xml"  
    cReadMode = "EMPTY" 
    cSchemaLocation = ? 
    lOverrideDefaultMapping = ? 
    cFieldTypeMapping = ? 
    cVerifySchemaMode = ?. 
  
returnValue = TEMP-TABLE ttCust:READ-XML(cSourceType, cFile, cReadMode,  
                                         cSchemaLocation, 
                                         lOverrideDefaultMapping,  
                                         cFieldTypeMapping, cVerifySchemaMode). 
IF returnValue THEN 
    FOR EACH ttCust: 
        DISPLAY CustNum NAME FORMAT "X(30)". 
    END.

Compare your results to those shown and explained in Table 22–6.

Table 22–6: Read mode examples
Read mode	List result	Explanation
`EMPTY`		The method empties the existing data from the temp-table. It then reads the XML document and adds records to the temp-table. Since the temp-table is empty, there is no duplicate key conflict. The result is a temp-table with records exclusively from the XML document.
`APPEND`		The method reads the XML document and adds records to the temp-table. Due to the duplicate key conflict between the record in the temp-table and the record in the XML with a `CustNum` of 1, the method stops loading records and stops. Records added before a conflict is detected are retained. Progress displays the error message shown.
`MERGE`		The method reads the XML document and adds records to the temp-table. Due to the duplicate key conflict between the record in the temp-table and the record in the XML with a `CustNum` of 1, the record from the XML is ignored. The result is all the existing records from the temp-table, plus all of the records from the XML except for the record with a `CustNum` of 1.
`REPLACE`		The method reads the XML document and adds records to the temp-table. Due to the duplicate key conflict between the record in the temp-table and the record in the XML with a `CustNum` of 1, the record from the XML replaces the record in the temp-table. The result is all the existing records from the temp-table except for the record with a `CustNum` of 1, plus all of the records from the XML.

Note: To see a version of the last example that uses a dynamic temp-table, look at the following sample files: pi-tfx-ttSetup-6.i and pi-tfx-read-6.p